<?

require_once '../include/common.inc.php';
//error_reporting(E_ALL);

if (!checkAdmin()) {
    header("LOCATION:index.php");
    exit;
}

$menu_index = 1;
$page_title = 'JANSEN';
$guide_message = 'Product | Basic Infomation | Import Data';

getMyParam('importtype');

$error_str = '';
$upload_file = "";


if ($importtype == '1' || $importtype == '2') {
		
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "xls" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/vnd.ms-excel" || $_FILES['upload_file']['type'] == "application/octet-stream")){
        $upload_file = $_FILES['upload_file']['tmp_name'];

        // =============== excelתΪ start=======================
        $user_method = 1;
        require_once '../include/reader.php';
        $data = new Spreadsheet_Excel_Reader();
        //$data->setOutputEncoding('CP1251');
       // $data->setOutputEncoding('gb2312');
		$data->setOutputEncoding('utf-8');
        $data->read($upload_file);
        $tmp_ary = $data->sheets[0]['cells'];

		if (is_array($tmp_ary) || $tmp_ary[1][1] == 'Web code nr') {
            foreach ($tmp_ary as $k1=>$v1) {
                $tmp2_ary = array();
                foreach ($v1 as $k2=>$v2) {
                    $tmp2_ary[$k2-1] = $v2;
                }
                $excel_ary[$k1-1] = $tmp2_ary;
            }
        } else {
            $excel_ary = '';
        }
        // 2:ʹ execl.class.php
		if (!is_array($excel_ary) || $excel_ary[0][0] != 'Web code nr') {
            $user_method = 2;
            require_once '../include/excel.class.php';
            Read_Excel_File($upload_file, $return);

            if (is_array($return)) {
                $index = 0;
                foreach ($return as $k=>$v) {
                    if ($index == 0) {
                        $excel_ary = $v;
                    }
                    $index++;
                }
            } else {
                $excel_ary = '';
            }
        }
        // 3:phpɵexecl
		if (!is_array($excel_ary) || $excel_ary[0][0] != 'Web code nr') {
            $user_method = 3;
            $excel_ary = array();
            $excel_ary1 = file($upload_file);

            foreach ($excel_ary1 as $k=>$v) {
                $tmp_ary = explode("\t", $v);
                $excel_ary[$k] = $tmp_ary;
            }
        }
        // =============== excelתΪ end=======================
		//Item#  === Web code nr

		if (trim($excel_ary[0][0]) != 'Web code nr') {   // Ψһ
            $error_str .= "<div class='js_msg'>excel error in line: code</div>";
        }

    } elseif ($_FILES['upload_file']['size'] > 0) {
        $error_str .= "<div class='js_msg'>excel file error</div>";
    }

    
    // excelкַ''ʱʾĴ 
    /*
    $string = htmlentities($excel_ary[1][1]);
    if (strstr($string, '&iexcl;&macr;')) {
        //$string = str_replace('&iexcl;&macr;', '\'', $string);echo $string;exit;
        $string = str_replace('&iexcl;&macr;', '', $string);echo $string;exit;
    } 
    
    $string = preg_replace('~&#x([0-9a-f]+);~ei', 'chr(hexdec("\\1"))', $string);
    $string = preg_replace('~&#([0-9]+);~e', 'chr(\\1)', $string); 
    echo $string;exit;
    //echo $excel_ary[1][1];exit;
    //echo strip_tags($excel_ary[1][1]);exit;
    //$excel_ary[1][1] = html_entity_decode($excel_ary[1][1], ENT_QUOTES);echo $excel_ary[1][1];
    $excel_ary[1][1] = hexdec($excel_ary[1][1]);echo $excel_ary[1][1];
    str_replace("&iexcl;&macr;", ":", $excel_ary[1][1]);echo $excel_ary[1][1];exit;
    */
    
    
    
    //echo $user_method; echo "<hr>"; echo $error_str; echo "<hr>"; echo "<pre>";print_r($excel_ary);exit;

}


if ($importtype == '1' && $error_str == "") {
	
    // ==================== ж start ==============================

//$product_hava_ary   
// $this_fid

//Finishes_Category  $aryFinishesID = array();
//Setting	$arySettingID = array();
//Style	$aryStyleID = array();
//Collection	$aryCollectionID = array();

//$excel_sql_ary Common.admin的数组
//excel_ary  excel 的数组
//$sql_product_ary  二维数组 临时存放产品的数组
//$sql_category_ary  //索引表
//$cat_col_ary  类别数组 ;
// $product_Category_ary 产品类别数组
	
	


    // вƷID:CODE=>ID
	//取得产品 CODE为索引的产品 ID列表
    $product_hava_ary = array();
    $sql = "SELECT `id`, `code` FROM `product`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        $rs['code'] = trim(strtolower($rs['code']));
        $product_hava_ary[$rs['code']] = $rs['id'];
    }

    // *赱ǰexcelΪcategory.xls,ȡøcategoryϢ
	//取得类型值，从文件的名称获取. 取出类别ID号 
    $category_name = basename($_FILES['upload_file']['name'], ".xls");
	$category_name = str_replace('_',' ',trim($category_name));	
	
	$sql = "SELECT `id`,`name` FROM `category` WHERE `fid` = 0";
	$query = $db->query($sql);	
	while($rs=$db->fetch_array($query))
	{
		if(preg_replace("/<[^>]+>/","",$rs["name"])==$category_name)
		{
			//ǰID
			$this_fid = $rs["id"];	
		}	
	}
	if (empty($this_fid)) {
        $error_str .= "<span class='js_msg'>Category error </span>";
    }
  
  $product_Category_ary = array();
  $product_Category_ary_arr = array();
    $sql = "SELECT `product_id`,`category_id`,`seq` FROM `product_category`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {        
        $product_Category_ary[] = $rs['product_id'];
		$product_Category_ary_arr[$rs['product_id']][]=$rs['category_id'];
    }
  	// 取得产品类别

/*foreach($product_Category_ary_arr as $k=>$vas)
{
   echo $k."_";
   if(is_array($vas))
   {
     foreach($vas as $kk=>$vt)
	 {
	   echo $vt."/";	 
	 }
     echo "<br/>";
   }

}*/

	
	
	//Add by Leo 2010-07-07:
	//$cat_col_ary+ո+ɵ Key => ID
	$cat_col_ary = array(); 
	$aryIn = get_category(0);
	GetProductCateIDAryByID($aryIn,$this_fid,&$cat_col_ary);
	//ǰIDڵIDַ
	$str_sub_cateid = GetCategoryTreeIDS($aryIn,$this_fid,false);
	
	//add by martin
	$category_col_ary = array(); 
	newGetProductCateIDAryByID($aryIn,$this_fid,&$category_col_ary);
	
	//Finishes
	$aryFinishesID = array();
	$sql = "SELECT `id`, `name` FROM `p_finish_set`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryFinishesID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//Setting
	$arySettingID = array();
	$sql = "SELECT `id`, `name` FROM `p_setting`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$arySettingID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//Style
	$aryStyleID = array();
	$sql = "SELECT `id`, `name` FROM `p_style`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryStyleID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//Collection
	$aryCollectionID = array();
	$sql = "SELECT `id`, `name` FROM `p_collection`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryCollectionID[strtolower($rs['name'])] = $rs["id"];
	}
	//category 
	
	$Category_All_Arr = array();
	$Category_All_NameId=array();
	$sql = "SELECT `id`, `fid`,`code`,`name` FROM `category`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$Category_All_Arr[$rs['id']] = $rs["fid"];
		$Category_All_NameId[$rs["name"]]=$rs['id'];
	}
	


    $category_index = 1;	
	
/*	foreach($category_col_ary as  $k=>$v)
	{
	  echo $k."<br/>";
	}*/
	//$excel_sql_ary Common.admin的数组
	//excel_ary  excel 的数组
	//$sql_product_ary  二维数组 临时存放产品的数组
	//$sql_category_ary
	//$cat_col_ary  类别数组 ;
	
	//ͨм martin
	//取第一行的值
	
	//开始填充数据
	//首行代码
    foreach ($excel_ary[0] as $k=>$v) {		
        $v = (string)$v;
		
		//ȥո
        if(preg_match("/(.+[^\s]+)[\s]+$/i", $v, $match_ary)){
            $v = $match_ary[1];
        }
		
		
	//echo $v."<br/>";
	 //	debug($excel_ary[0],$excel_sql_ary,$cat_col_ary,$excel_category_column);
           
        if ($k>0 && trim($v) != "") {
            if (!array_key_exists($v, $excel_sql_ary) && !array_key_exists($v, $cat_col_ary) && !array_key_exists($v, $excel_category_column)) {
                $error_str .= "<div class='js_msg'>excel error in line: " . $v . "</div>";
			
            } elseif (array_key_exists($v, $excel_sql_ary)) {
				//Excel ȡһУȡӦֵֶ
				//把值赋值给$sql_product_ary    (索引$k 为$excel_sql_ary 的值)
				//echo $excel_sql_ary[$v].$k."<br>";
                $sql_product_ary[$excel_sql_ary[$v]] = array('sql_col'=>$excel_sql_ary[$v], 'col_num'=>$k);
				//echo $excel_sql_ary[$v].$sql_product_ary[$excel_sql_ary[$v]]['col_num']."<br>";
				//echo $excel_sql_ary[$v].$k."<br/>";
            } 
			
			if (array_key_exists($v, $excel_category_column )) {
                $k = trim($k);			
                //$sql_category_ary[] = array('categoryid'=>$cat_col_ary[$v], 'col_num'=>$k);
				$sql_category_ary[$excel_category_column[$v]] = array('sql_col'=>$excel_category_column[$v], 'col_num'=>$k);
				
            }
        }
    }	
	//首行代码结束	
 
//debug($excel_ary);
    /*
    echo "<pre>";
    echo $error_str;   // Ŀ

    //print_r($product_hava_ary);   // вƷcode
    //print_r($excel_sql_ary);
    //print_r($cat_col_ary);


    //print_r($sql_product_ary);
    //print_r($sql_category_ary);

    print_r($excel_ary);
    exit;
    */

    // ==================== ж end ==============================

	//	$excel_sql_ary 
    
	//判断是否有数据
    if (empty($excel_ary[1])) {
        $error_str .= "<span class='js_msg'>excel error</span>";
    }

//填充到数据库
    if ($error_str == "") {
        $code_ary = array();
        $pre_product_id = 0;//ѭǰһƷIDʼΪ0
	
		//$cur_key 当前行
		$cur_key = 0;
		
		//循环整个EXCEL表
		$codePRE="";
		foreach ($excel_ary as $k=>$v)
		{
					//category
			//excel 表列循环
			
			//part1 start
			if ($k>0 && (isset($v[0]) && trim($v[0]) != "") &&  (isset($v[28]) )){	
				    $cur_key = $k; 				
				   
				}else{		
							
				// 不知道何用意
				if($k != '0' && (isset($v[27]) && trim($v[27]) != "")){								
						$code = (string)$excel_ary[$cur_key][0];
						//edit by martin				
					$v[$sql_category_ary['category_id']['col_num']] = trim($v[$sql_category_ary['category_id']['col_num']]) == "" ? $this_fid : $category_col_ary[$v[$sql_category_ary['category_id']['col_num']]]; 
						$t_seq = $v[$sql_category_ary['seq']['col_num']];						 
						$t_product_id = $product_hava_ary[strtolower($code)];						
						$t_category_id = $v[$sql_category_ary['category_id']['col_num']];
						$t_sql = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('$t_product_id', '$t_category_id', '$t_seq')";				
						$db->query($t_sql);		
					}					
				
				}				
			//part1 end
				
				
            //if ($k>0 && ( (isset($v[0]) && trim($v[0]) != "") ||  (isset($v[count($excel_sql_ary)]) && trim($v[count($excel_sql_ary)]) != "") )){     
			
			//填充类别 add by martin
			if($k>0&&isset($v[0])&&trim($v[0]) != "")
			{			   
			  // echo $v[0]."_".$v[27]."<br/>";
			   $codePRE=$v[0];
			}
			else
			{
			  // echo $codePRE."++ ".$v[27]."<br/>";
			 
			 	if(!empty($v[$sql_product_ary['sub_id']['col_num']]))
						{
						
					
							
						$strt =GetFid($Category_All_Arr,$Category_All_NameId[$v[$sql_product_ary['sub_id']['col_num']]]);	
						$subar=array();
						$subar=explode(",",$strt);
						//echo count($subar)."<br/>";
						
						//echo $str."<br/>";
						foreach($subar as $ky=>$vb)
						{
						  //$st= implode(",",$product_Category_ary_arr[$product_hava_ary[strtolower($code)]]);
						 //  echo $st."<br/>"; 
						   if($vb!="100000"&&!in_array($vb,$product_Category_ary_arr[$product_hava_ary[strtolower($codePRE)]]))
						   {		
						   	 	   						   						   
						    $sqlsub = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($codePRE)] . "', '" . $vb . "', '$category_index')";							 				
                          // echo $sqlsub;
						   
						    //$db->query($sqlsub);
                            //$category_index += 1;							
							
						   }
						
						}
						 
						
						}
						
						
						
			}
			
			
			if ($k>0 && (isset($v[0]) && trim($v[0]) != ""))
			{
			
                $code = (string)$v[0];	
				
				
				//每次填充，记录一次 	$code_ary	
				if (in_array(strtolower($code), $code_ary) && $code!="") {   // code ΨһԼ
                    $error_str .= "<div class='js_msg'>Code " . $code . " (" . $v[1] . ") have set.</div>";
                } else {
					//ʵһΣͰcodeŵб
					$code_ary[] = strtolower($code);
					//Ϊµexcelʽ:ͬһƷͬڶʱ
                   // if ((!empty($sql_product_ary) && trim($v[0]) != "") || (trim($v[0]) == "" && trim($v[count($excel_sql_ary)]) != "")) {
				   
				 
				   //索引表
					if ((!empty($sql_product_ary) && trim($v[0]) != "") || (trim($v[0]) == "" && trim($v[28]) != "")) {
					//echo $v[28]."<br>";
						
					// 列号	$sql_product_ary['hidden_flag']['col_num']
                        $v[$sql_product_ary['hidden_flag']['col_num']] = (strtolower($v[$sql_product_ary['hidden_flag']['col_num']]) == 'y') ? 0 : 1;	
                       	$v[$sql_product_ary['new_flag']['col_num']] = (strtolower($v[$sql_product_ary['new_flag']['col_num']]) == 'y') ? 1 : 0;
                        $v[$sql_product_ary['pic']['col_num']] = strstr(strtolower($v[$sql_product_ary['pic']['col_num']]), ".jpg") ? $v[$sql_product_ary['pic']['col_num']] : "";
                        $v[$sql_product_ary['small_pic']['col_num']] = strstr(strtolower($v[$sql_product_ary['small_pic']['col_num']]), ".jpg") ? $v[$sql_product_ary['small_pic']['col_num']] : "";
					
					

                        if (!empty($v[$sql_product_ary['small_pic']['col_num']]) && !strpos($v[$sql_product_ary['small_pic']['col_num']], 's-') && $v[$sql_product_ary['pic']['col_num']] == $v[$sql_product_ary['small_pic']['col_num']] ) {
                            if (strpos($v[$sql_product_ary['small_pic']['col_num']], "-s.")) {
                                $v[$sql_product_ary['small_pic']['col_num']] = str_replace("-s.", ".", $v[$sql_product_ary['small_pic']['col_num']]);
                            }
                            $v[$sql_product_ary['small_pic']['col_num']] = "s-" . $v[$sql_product_ary['small_pic']['col_num']];
                        }

                        $v[$sql_product_ary['length2']['col_num']] = is_float($v[$sql_product_ary['length2']['col_num']]) ? $v[$sql_product_ary['length2']['col_num']] : $v[$sql_product_ary['length']['col_num']]/2.54;
                        $v[$sql_product_ary['height2']['col_num']] = is_float($v[$sql_product_ary['height2']['col_num']]) ? $v[$sql_product_ary['height2']['col_num']] : $v[$sql_product_ary['height']['col_num']]/2.54;
                        $v[$sql_product_ary['width2']['col_num']] = is_float($v[$sql_product_ary['width2']['col_num']]) ? $v[$sql_product_ary['width2']['col_num']] : $v[$sql_product_ary['width']['col_num']]/2.54;

                        $v[$sql_product_ary['cuft']['col_num']] = is_float($v[$sql_product_ary['cuft']['col_num']]) ? $v[$sql_product_ary['cuft']['col_num']] : $v[$sql_product_ary['cbm']['col_num']]/0.0283168;
						
						
						
						  											
                       					
						$v[$sql_product_ary['comment']['col_num']] = strtoupper(substr($v[$sql_product_ary['comment']['col_num']],0,1)).substr($v[$sql_product_ary['comment']['col_num']],1);							
                    
					    //first letter to upper	
					   //name
					     $string_tmp = htmlentities($v[$sql_product_ary['name']['col_num']]);	
                        if (strstr($string_tmp, '&iexcl;&macr;')) {
                            $string_tmp = @str_replace('&iexcl;&macr;', '\'', $string_tmp);
                            $string_tmp = @str_replace('&iexcl;&reg;', '\'', $string_tmp);
                        }
                        if (strstr($string_tmp, '&iexcl;&deg;')) {
                            $string_tmp = @str_replace('&iexcl;&deg;', '"', $string_tmp);
                            $string_tmp = @str_replace('&iexcl;&plusmn;', '"', $string_tmp);
                        }
						$string_tmp = html_entity_decode($string_tmp);
						
						if(substr($string_tmp,0,1) == '"') $string_tmp = substr($string_tmp,1);
						if(substr($string_tmp,count($string_tmp)-1) == '"') $string_tmp = substr($string_tmp,0,count($string_tmp)-1);
						$string_tmp = strtoupper(substr($string_tmp,0,1)).substr($string_tmp,1);
					
                        $v[$sql_product_ary['name']['col_num']] = $string_tmp;
						
						
						
						
						//鵼Ʒ,Finish,Setting,Style,Collection¼¼
						
						//echo $v[$sql_product_ary['finish_idzh']['col_num']];
						//Finishes
						if($v[$sql_product_ary['finish_id']['col_num']]!="")
						{
							//echo $v[$sql_product_ary['finish_id']['col_num']];
							$v[$sql_product_ary['finish_id']['col_num']] = strtolower($v[$sql_product_ary['finish_id']['col_num']]);
							if(!array_key_exists($v[$sql_product_ary['finish_id']['col_num']],$aryFinishesID))
							{
								$sql4 = "INSERT INTO `p_finish_set` ( `name` , `createtime` ) VALUES ('".$v[$sql_product_ary['finish_id']['col_num']]."', '".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$v[$sql_product_ary['finish_id']['col_num']] = $aryFinishesID[$v[$sql_product_ary['finish_id']['col_num']]] = $db->insert_id();
							}
							else
							{
								$v[$sql_product_ary['finish_id']['col_num']] = $aryFinishesID[$v[$sql_product_ary['finish_id']['col_num']]];
							}
						}					
						
						//Setting
						if($v[$sql_product_ary['setting_id']['col_num']]!="")
						{
							
							$v[$sql_product_ary['setting_id']['col_num']] = strtolower($v[$sql_product_ary['setting_id']['col_num']]);
							$v[$sql_product_ary['setting_idzh']['col_num']] = htmlentities($v[$sql_product_ary['setting_idzh']['col_num']]);
							
							if(!array_key_exists(strtolower($v[$sql_product_ary['setting_id']['col_num']]),$arySettingID) && $v[$sql_product_ary['setting_id']['col_num']]!="")
							{
								$sql4 = "INSERT INTO `p_setting` (`name`,`namezh`,`createtime`) VALUES('".$v[$sql_product_ary['setting_id']['col_num']]."','".$v[$sql_product_ary['setting_idzh']['col_num']]."','".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$v[$sql_product_ary['setting_id']['col_num']] = $arySettingID[$v[$sql_product_ary['setting_id']['col_num']]] = $db->insert_id();
							}
							else
							{
								$v[$sql_product_ary['setting_id']['col_num']] = $arySettingID[$v[$sql_product_ary['setting_id']['col_num']]];
							}
							
							
							
						}
						
						//Style
						if($v[$sql_product_ary['style_id']['col_num']]!="")
						{
							$v[$sql_product_ary['style_id']['col_num']] = strtolower($v[$sql_product_ary['style_id']['col_num']]);
							if(!array_key_exists(strtolower($v[$sql_product_ary['style_id']['col_num']]),$aryStyleID) && $v[$sql_product_ary['style_id']['col_num']]!="")
							{
								$sql4 = "INSERT INTO `p_style` (`name`,`createtime`) VALUES('".$v[$sql_product_ary['style_id']['col_num']]."','".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$v[$sql_product_ary['style_id']['col_num']] = $aryStyleID[$v[$sql_product_ary['style_id']['col_num']]] = $db->insert_id();
							}
							else
							{
								$v[$sql_product_ary['style_id']['col_num']] = $aryStyleID[$v[$sql_product_ary['style_id']['col_num']]];
							}
						}
						
						//Collection
				       		if($v[$sql_product_ary['collection_id']['col_num']]!="")
						{
							$v[$sql_product_ary['collection_id']['col_num']] = strtolower($v[$sql_product_ary['collection_id']['col_num']]);
							if(!array_key_exists(strtolower($v[$sql_product_ary['collection_id']['col_num']]),$aryCollectionID) && $v[$sql_product_ary['collection_id']['col_num']]!="")
							{
								$sql4 = "INSERT INTO `p_setting` (`name`,`createtime`) VALUES('".$v[$sql_product_ary['collection_id']['col_num']]."','".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$v[$sql_product_ary['collection_id']['col_num']] = $aryCollectionID[$v[$sql_product_ary['collection_id']['col_num']]] = $db->insert_id();
							}
							else
							{
								$v[$sql_product_ary['collection_id']['col_num']] = $aryCollectionID[$v[$sql_product_ary['collection_id']['col_num']]];
							}	
						}										
						
							
				
				
						
					//product 	
                        if (array_key_exists(trim(strtolower($code)), $product_hava_ary)) {
						$codePRE=$code;
                            $update_sql_ary = array();
							
                            foreach ($sql_product_ary as $k1=>$v1)
							 {		
						        //add by martin 2010 -12 -1	Category_id					   						
				                if($k1=="Category_idzh"||$k1=="Sub_idzh"||$k1=="setting_idzh"||$k1=="style_idzh"||$k1=="sub_id"||$k1=="Category_id")                                    continue;
                               // echo $v1['sql_col']."<br/>";
							     $update_sql_ary[] = "`" . $v1['sql_col'] . "` = '" . addslashes($v[$v1['col_num']]) . "'";								
					              
                            }
							
                            $sql = "UPDATE `product` SET " . implode(",", $update_sql_ary) . ", `delete_flag`="."'0'".", `cate_seq`=$k WHERE `code` = '" . $code . "'";
											
                            $db->query($sql);
                        } else {

                            $sql_ary_v = $sql_ary_value = array();
                            foreach ($sql_product_ary as $k1=>$v1) {
							if($k1=="Category_idzh"||$k1=="Sub_idzh"||$k1=="setting_idzh"||$k1=="style_idzh"||$k1=="sub_id"||$k1=="Category_id")                            continue;
							
                                $sql_ary_v[] = "`" . $v1['sql_col'] . "`";
                                $sql_ary_value[] = "'" . addslashes($v[$v1['col_num']]) . "'";
                            }
                            $sql = "INSERT INTO `product` (`code`,`cate_seq`, " . implode(",", $sql_ary_v) . ") VALUES ('" . $code . "', " .$k.",". implode(",", $sql_ary_value) . ")";

                            $db->query($sql);
                            $product_hava_ary[strtolower($v[0])] = $db->insert_id();

                        }
                    }
					
							
					
							//Category 						
						$v[$sql_product_ary['category_id']['col_num']] = trim($v[$sql_product_ary['category_id']['col_num']]) == "" ? $this_fid : $category_col_ary[$v[$sql_product_ary['category_id']['col_num']]];
						
						
									
						//sub Category
						$v[$sql_product_ary['sub_id']['col_num']] = trim($v[$sql_product_ary['sub_id']['col_num']]) == "" ? $this_fid : $category_col_ary[$v[$sql_product_ary['sub_id']['col_num']]];
						
						if(!empty($v[$sql_product_ary['sub_id']['col_num']]))
						{
							//$Category_All_Arr
						//$str =$v[$sql_product_ary['sub_id']['col_num']].",";
						$str =GetFid($Category_All_Arr,$v[$sql_product_ary['sub_id']['col_num']]);								
						$subar=array();
						$subar=explode(",",$str);
						//echo count($subar)."<br/>";						
						
						foreach($subar as $ky=>$vb)
						{
						  //$st= implode(",",$product_Category_ary_arr[$product_hava_ary[strtolower($code)]]);
						 //  echo $st."<br/>"; 
						   if($vb!="100000"&&!in_array($vb,$product_Category_ary_arr[$product_hava_ary[strtolower($code)]]))
						   {		
						   	 	   						   						   
						    $sqlsub = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $vb . "', '$category_index')";							 				
                            $db->query($sqlsub);
                            $category_index += 1;							
							
						   }
						
						}
						 
						
						}
					
						
						
						$category_array=array();
						$category_array=explode(";", $v[$sql_product_ary['sub_id']['col_num']]);
						$category_array[]=$v[$sql_product_ary['category_id']['col_num']];	
						
										
					
					
					//product_category 更新类别表 
                   
                }//202:else end
				
				
            }//198:  if ($k>0 && isset($v[0]) && trim($v[0]) != "") 			
			
			
        } //196: foreach ($excel_ary as $k=>$v)
		
		    
		
		//EXCELƷĸƷ
		$sql="select id,code from product where id in(select  product_id from `product_category` where category_id in (".$str_sub_cateid."))";//ԭĲƷ
		$query = $db->query($sql);
		$product_in_category = array();
		while($rs = $db->fetch_array($query)) 
		{
			$product_in_category[$rs["code"]] = $rs["id"];
		}
		
		foreach($product_in_category as $product_code => $product_id)
		{
			if(!in_array(strtolower($product_code),$code_ary))
			{
				$sql3 = "update `product` set hidden_flag = 1 where `id` = $product_id";
				$db->query($sql3);
			}
		}
    }//193: if ($error_str == "")
	
	
	
	
    // ================= end import excel ==========================


   

} elseif ($importtype == '2' && $error_str == "") {

    // ============================= import inventory data start==========================
    if ($excel_ary[0][1] != "Qty") {
        $error_str = "<div class='js_msg'>This isn't a inventory excel !</div>";
    } else {
        //if ($excel_ary[0][2] == "HKWHSP(USD)" && $excel_ary[0][3] == "HKWHSP(HKD)") {
		if ($excel_ary[0][2] == "Special Price" && $excel_ary[0][3] == "Special Price2") {
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0])  && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0) {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }
                    if (!isset($v[3])) {
                        $v[3] = 0;
                    }
                    $v[0] = strtolower($v[0]);
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp1'] = $v[2];
                    $code_ary[$v[0]]['sp2'] = $v[3];
					$code_ary[$v[0]]['inventory_name'] = trim($v[4]);
					$inventory_name_ary[] = trim($v[4]);
                }
            }

            //$inventory_name_ary = array(0=>"Indonesia Warehouse",1=>"Singapore Show Container");
            //echo "<pre>";print_r($code_ary);//exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
	
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);//code X
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    //$id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$id_code_ary[$rs['id']]['inventory_name']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }

                // жЩcode
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp1'] . "', `special_price2` ='" . $v['sp2'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "';";			
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`, `special_price2`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp1'] . "', '" . $v['sp2'] . "');";
                        $query = $db->query($sql);
                    }
                }
            }

            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

        } else {
            // ============ ݸʽΪ start ========================
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0]) && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0 && $v[3] != '') {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }


                    $v[0] = strtolower($v[0]);
                    $v[2] = strtolower($v[2]) == 'nil' ? 0 : $v[2];
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp'] = $v[2];
                    $code_ary[$v[0]]['inventory_name'] = trim($v[3]);
                    $inventory_name_ary[] = trim($v[3]);
                }
            }

            //echo "<pre>";print_r($inventory_name_ary);exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }
                // жЩcode
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "'";
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp'] . "')";	
                        $query = $db->query($sql);
                    }
		        }
            }
            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

            // ============================= import inventory data end==========================
        }
    }




} elseif ($importtype == '3') {
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "zip" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/x-zip-compressed")){

        require_once '../include/zip.class.php';

        $upload_file = $_FILES['upload_file']['tmp_name'];
        $pic_file_name = "../attachments/" . $_FILES['upload_file']['name'];
        if (!move_uploaded_file($_FILES['upload_file']['tmp_name'], $pic_file_name)) {
            // ļʧ
            $error_str = 'upload zip file failed<br>';
        } else {
            $tmp_file2 = '../attachments/';
            $z=new Zip;
            $z->Extract($pic_file_name, $tmp_file2);
            @unlink($pic_file_name);
        }
    } elseif ($_FILES['upload_file']['size'] > 0) {
        $error_str .= "<div class='js_msg'>picture file error</div>";
    }
}


if ($upload_file != "" && $error_str == '') {
    $error_str = "<span class='red_font'>Import data Succeed</span>";
}

/*
$debug = 1;
debuginfo();
echo "<pre>";
print_r($db->sql_ary);
*/

include_once(JANSEN_ROOT . 'admin/menu_list.php');
require_once PrintEot('a_header');
require_once PrintEot('a_p_import_excel');
require_once PrintEot('a_footer');
?>
